﻿'Author : Jawad Najim
'Date : 01/29/2012


Imports System.Data.SqlClient

Public Class DARSDBConnection
    ' 
    Dim SqlConnectString As String = "server=dwarf.cis.temple.edu;Database=FA11_4296_BeyondDARS; User ID =tuc30056;Password=sisalwa1"
    Dim myConnectionSql As SqlConnection
    Dim objCmd As SqlCommand
    Dim objReader As SqlDataReader
    Dim ds As DataSet
    Dim dt As DataTable
    Dim myDataAdapter As SqlDataAdapter
    Dim drow As DataRow
    Public Sub New()
        Try
            myConnectionSql = New SqlConnection(SqlConnectString)
            myConnectionSql.Open()
        Catch ex As Exception
            Return
        End Try
    End Sub

    '''<summary>
    '''Input Parameter is a Select statement. Return is the Dataset 
    '''Note: The Dataset is also stored as a class variable for use 
    '''in the getField function
    ''' </summary>
    Public Function getDataSet(ByVal SqlSelect As String) As DataSet
        
        myDataAdapter = New SqlDataAdapter(SqlSelect, myConnectionSql)
        ds = New DataSet
        myDataAdapter.Fill(ds)

        Return ds

    End Function

    '''<summary>
    '''Input Parameter is a Select statement. Return is the Datatable 
    '''Note: The Datatable is also stored as a class variable for use 
    '''in the getField function
    ''' </summary>
    Public Function getDataTable(ByVal SqlSelect As String) As DataTable

        myDataAdapter = New SqlDataAdapter(SqlSelect, myConnectionSql)
        dt = New DataTable
        myDataAdapter.Fill(dt)

        Return dt

    End Function

    '''<summary>
    '''Input : a Select statement.
    '''  Returns DataReader
    ''' </summary>
    Public Function getDataReader(ByVal SqlSelect As String) As SqlDataReader

        objCmd = New SqlCommand(SqlSelect, myConnectionSql)
        Return objCmd.ExecuteReader()
    End Function

    '''<summary>
    '''Input: a Command object containing a SQL Select statement. 
    '''Returns a DataSet.
    '''This function is used for passing parameters to a Stored Procedure
    ''' </summary>
    Public Function getDataSetUsingCmdObj(ByVal aCmdObj As SqlCommand) As DataSet
      
        aCmdObj.Connection = myConnectionSql
        myDataAdapter = New SqlDataAdapter(aCmdObj)
        ds = New DataSet
        myDataAdapter.Fill(ds)

        Return ds
    End Function

    '''<summary>
    '''Input is a Command object containing a SQL Select statement. 
    '''Returns a Datatable.
    '''This function is used for passing parameters to a Stored Procedure
    ''' </summary>
    Public Function getDataTableUsingCmdObj(ByVal aCmdObj As SqlCommand) As DataTable
        
        aCmdObj.Connection = myConnectionSql
        myDataAdapter = New SqlDataAdapter(aCmdObj)
        dt = New DataTable
        myDataAdapter.Fill(dt)

        Return dt
    End Function

    '''<summary>
    '''Input are (1)a DataSet and (2) the zero based row of the 
    '''table in the DataSet to be returned.  Returns a row.
    ''' </summary>
    Public Function getDataRow(ByVal dset As DataSet, ByVal row As Integer) As DataRow  
        Dim objTable As DataTable = dset.Tables(0)
        drow = objTable.Rows(row)
        Return drow
    End Function

    '''<summary>
    '''Input are (1)a Datatable and (2) the zero based row of the 
    '''datatable .  Returns a row.
    ''' </summary>
    Public Function getDataRow(ByVal dtable As DataTable, ByVal rowIndex As Integer) As DataRow
        drow = dtable.Rows(rowIndex)
        Return drow
    End Function

    '''<summary>
    '''Input : (1)a Field (Column) name as a string and
    '''(2)the zero based row of the table in the DataSet 
    '''from which the field is to be extracted. Returns the value 
    '''in the field as a variant type.
    '''This function assumes that one of the getDataSet functions
    '''had been called, thus producing a ds at the class level.
    ''' </summary>
    Public Function getField(ByVal FieldName As String, ByVal rowIndex As Integer) As Object

        dt = ds.Tables(0)
        drow = dt.Rows(rowIndex)
        Return drow(FieldName)
    End Function

    '''<summary>
    '''Input : (1)a Field (Column) name as a string and
    '''(2)the zero based row of the table in the DataSet 
    '''from which the field is to be extracted. Returns the value 
    '''in the field as a variant type.
    '''This function assumes that one of the getDatatable functions
    '''had been called, thus producing a ds at the class level.
    ''' </summary>
    Public Function getFieldValue(ByVal ColumnName As String, ByVal rowIndex As Integer) As Object
        drow = dt.Rows(rowIndex)

        Return drow(ColumnName)
    End Function

    '''<summary>
    '''Input: (1) index of the column in the datatable (2) index of the row in the datatable
    '''Return : value of the of the column
    '''Note : to be used after one of the getDatatable function was called
    ''' </summary>
    Public Function getFieldValue(ByVal Columnindex As Integer, ByVal rowIndex As Integer) As Object
        drow = dt.Rows(rowIndex)
        Return drow(Columnindex)
    End Function
    '''<summary>
    '''Input: (1) index of the column in the datatable (2) index of the row in the datatable
    ''' Return : the value of the column
    '''Note : to be used after one of the getDataset function was called
    ''' </summary>
    Public Function getField(ByVal FieldIndex As Integer, ByVal rowIndex As Integer) As Object
        dt = ds.Tables(0)
        drow = dt.Rows(rowIndex)
        Return drow(FieldIndex)
    End Function

    Public Sub closeConSql()
        myConnectionSql.Close()
    End Sub

    Public Sub ResetConnection()
        myConnectionSql.Close()
        myConnectionSql.Open()
    End Sub

    '''<summary>
    '''InParam is a SQL manipulate statement (Insert, Update, Delete). 
    '''Returns the number of rows affected by the update.
    '''If an Exception occurs, -1 is returned
    ''' </summary>
    Public Function doUpdate(ByVal SqlManipulate As String) As Integer
        
        objCmd = New SqlCommand(SqlManipulate, myConnectionSql)
        Try
            Return objCmd.ExecuteNonQuery()
        Catch e As Exception
            Return -1
        End Try
    End Function

End Class
